/***********************************************************************************************************************************
 * Name:	Database_Shrink-Log-Files-All-DBs.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:	Shrink log files in all databases. Handles multiple log files per DB.
 */
SET IMPLICIT_TRANSACTIONS OFF
GO
DECLARE
  @total			INT,
  @count			INT,
  @tsql_command		NVARCHAR(MAX),
  @tsql_template	NVARCHAR(MAX);
CREATE TABLE #dblogfilesdata (
	[database_name]		SYSNAME,
	[file_name]			SYSNAME,
	[size]				INT);
SET NOCOUNT ON;SET QUOTED_IDENTIFIER ON;

-- Phase I - Bring up all database file info into a single table
SET @tsql_template= N'WITH p AS (SELECT [object_id], [index_id], [partition_count]=COUNT(partition_id) FROM [?].sys.partitions GROUP BY [object_id], [index_id])
INSERT INTO #dblogfilesdata
SELECT [database_name]= ''?'', [name], [size] FROM [?].sys.database_files WHERE [state] = 0 AND [type] = 1';
DECLARE itemList1 CURSOR LOCAL FAST_FORWARD FOR
	SELECT tsql_command= REPLACE(@tsql_template,'?',[name])
	  FROM master.sys.databases
	  WHERE [state] = 0 AND 4 < database_id
	  ORDER BY database_id DESC;
OPEN itemList1;
WHILE 1=1 BEGIN;
  FETCH NEXT FROM itemList1 INTO @tsql_command;
  IF @@FETCH_STATUS = 0 EXECUTE (@tsql_command) ELSE BREAK;
END;
CLOSE itemList1;
DEALLOCATE itemList1;

-- Phase II - Shrink DB Logfiles
DECLARE itemList2 CURSOR LOCAL FAST_FORWARD FOR
	SELECT tsql_command= N'USE [' + database_name + N']; DBCC SHRINKFILE(''' + [file_name] + ''') WITH NO_INFOMSGS;'
	  FROM #dblogfilesdata ORDER BY database_name;
OPEN itemList2;
WHILE 1=1 BEGIN;
  FETCH NEXT FROM itemList2 INTO @tsql_command;
  IF @@FETCH_STATUS = 0 PRINT (@tsql_command) ELSE BREAK;
END;
CLOSE itemList2;
DEALLOCATE itemList2;

TRUNCATE TABLE #dblogfilesdata

-- Phase III - Repeat Phase I (New File Sizes)
SET @tsql_template= N'WITH p AS (SELECT [object_id], [index_id], [partition_count]=COUNT(partition_id) FROM [?].sys.partitions GROUP BY [object_id], [index_id])
INSERT INTO #dblogfilesdata
SELECT [database_name]= ''?'', [name], [size] FROM [?].sys.database_files WHERE [state] = 0 AND [type] = 1';
DECLARE itemList3 CURSOR LOCAL FAST_FORWARD FOR
	SELECT tsql_command= REPLACE(@tsql_template,'?',[name])
	  FROM master.sys.databases
	  WHERE [state] = 0 AND 4 < database_id
	  ORDER BY database_id DESC;
OPEN itemList3;
WHILE 1=1 BEGIN;
  FETCH NEXT FROM itemList3 INTO @tsql_command;
  IF @@FETCH_STATUS = 0 EXECUTE (@tsql_command) ELSE BREAK;
END;
CLOSE itemList3;
DEALLOCATE itemList3;

-- Phase IV - Set Growth and Size of DB Logfiles
DECLARE itemList4 CURSOR LOCAL FAST_FORWARD FOR
	SELECT tsql_command= N'ALTER DATABASE [' + database_name + N'] MODIFY FILE (NAME=''' + [file_name] + N''', SIZE=' + CAST(CEILING(([size]/128.0)/128.0)*128 AS NVARCHAR(10)) + ', FILEGROWTH=128MB, MAXSIZE=4GB);'
	  FROM #dblogfilesdata ORDER BY database_name;
OPEN itemList4;
WHILE 1=1 BEGIN;
  FETCH NEXT FROM itemList4 INTO @tsql_command;
  IF @@FETCH_STATUS = 0 PRINT (@tsql_command) ELSE BREAK;
END;
CLOSE itemList4;
DEALLOCATE itemList4;

SET NOCOUNT OFF;
DROP TABLE #dblogfilesdata;
GO